/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.nii.qlks.BusinessLogicLayer;

import edu.nii.qlks.DataAccessLayer.MSSQLConnectUnit;
import edu.nii.qlks.DataTransferObject.KhachHangInfo;
import edu.nii.qlks.DataTransferObject.LoaiKhachHangInfo;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.HashSet;
import java.util.ArrayList;

/**
 *
 * @author MARS_NHO
 */
public class KhachHangBLL {
    
    MSSQLConnectUnit _connect;
    ResultSet  _result;
 
    
    public ArrayList LayDS(String Condition, String OderBy) throws Exception{
        ArrayList vt = new ArrayList();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("KHACHHANG", Condition, OderBy);
        
        while (_result.next()){
            ArrayList vt_row = new ArrayList();
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(_result.getString(5));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public ArrayList LayDS(String Condition) throws Exception{
         return LayDS(Condition, null);
    }
    
    public ArrayList LayDS() throws Exception{
        return LayDS(null);
    }
    
    public void Insert(KhachHangInfo khachHangInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        HashMap<String, Object> map = new HashMap<>();
        map.put("MaKhachHang", khachHangInfo.getMaKhachHang());
        map.put("TenKhachHang", khachHangInfo.getTenKhachHang());
        map.put("MaLoaiKhach", khachHangInfo.getMaLoaiKhach());
        map.put("CMND", khachHangInfo.getCMND());
        map.put("DiaChi", khachHangInfo.getDiaChi());
        _connect.Insert("KHACHHANG", map);
    }
    
     
    public void Inserts(HashSet<KhachHangInfo> khachHangInfos) throws Exception{
        for (KhachHangInfo khachHangInfo:khachHangInfos) {
            this.Insert(khachHangInfo);
        }
    }
    
    public void Update(KhachHangInfo khachHangInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        HashMap<String, Object> map = new HashMap<>();
        map.put("TenKhachHang", khachHangInfo.getTenKhachHang());
        map.put("MaLoaiKhach", khachHangInfo.getMaLoaiKhach());
        map.put("CMND", khachHangInfo.getCMND());
        map.put("DiaChi", khachHangInfo.getDiaChi());
        _connect.Update("KHACHHANG", map, "MaKhachHang = '" + khachHangInfo.getMaKhachHang() + "'");
    }
    
    public void Updates(HashSet<KhachHangInfo> khachHangInfos) throws Exception{
        for (KhachHangInfo khachHangInfo:khachHangInfos){
            this.Update(khachHangInfo);
        }
    }
    
    public void Delete(KhachHangInfo khachHangInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _connect.Delete("KHACHHANG", "MaKhachHang = '" + khachHangInfo.getMaKhachHang() + "'");
    }
    
    public void Deletes(HashSet<KhachHangInfo> khachHangInfos) throws Exception{
         for (KhachHangInfo khachHangInfo:khachHangInfos){
            this.Delete(khachHangInfo);
        }
    }
    
    public String TaoMaTuDong(String maGoc, int batDau){
         
        return BusinessLogicAbstract.TaoMaTuDong(maGoc, batDau);
    }
    
    public ArrayList LayDS_Khachhang() throws Exception{
        ArrayList vt = new ArrayList();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select(" KHACHHANG.TenKhachHang, LOAIKHACHHANG.TenLoaiKhach, "
                + " PHONG.TenPhong,  convert(varchar, PHIEUTHUEPHONG.NgayThuePhong, 103), "
                + "  convert(varchar, HOADON.NgayThanhToan, 103), KHACHHANG.CMND, KHACHHANG.DiaChi",
                " PHONG, LOAIKHACHHANG, KHACHHANG, PHIEUTHUEPHONG, CHITIETPHIEUTHUE, HOADON ",
                " PHONG.MaPhong = PHIEUTHUEPHONG.MaPhong "
                + " and CHITIETPHIEUTHUE.MaPhieuThue =  PHIEUTHUEPHONG.MaPhieuThue"
                + " and CHITIETPHIEUTHUE.MaKhachHang = KHACHHANG.MaKhachHang "
                + " and HOADON.MaKhachHang = KHACHHANG.MaKhachHang "
                + " and KHACHHANG.MaLoaiKhach = LOAIKHACHHANG.MaLoaiKhach",
                "TenKhachHang ASC");
        int stt = 0;
        while (_result.next()){
            ArrayList vt_row = new ArrayList();
            stt++;
            vt_row.add(stt);
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(_result.getString(5));
            vt_row.add(_result.getString(6));
            vt_row.add(_result.getString(7));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public String[] LayTenKhachHang() throws Exception{
        
        String [] array = new String[100];
        int soItem = 0;
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("KHACHHANG");
        while (_result.next()){
            array[soItem] = (_result.getString(2));
            soItem++;
        }
        String [] array2 = new String[soItem];
        for (int j = 0; j < soItem; j++) {
                array2[j] = array[j].trim();
        }
        _connect.Close();
        return array2;
    }
    
    public String[] LayCMND() throws Exception{
        
        String [] array = new String[100];
        int soItem = 0;
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("KHACHHANG");
        while (_result.next()){
            array[soItem] = (_result.getString(4));
            soItem++;
        }
        String [] array2 = new String[soItem];
        for (int j = 0; j < soItem; j++) {
                array2[j] = array[j].trim();
        }
        _connect.Close();
        return array2;
    }
    
    public String[] LayTenLoaiKhachHang() throws Exception{
        String [] array = new String[100];
        int soItem = 0;
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select(" LOAIKHACHHANG.TenLoaiKhach ", "KHACHHANG, LOAIKHACHHANG"
                , " LOAIKHACHHANG.MaLoaiKhach = KHACHHANG.MaLoaiKhach", null);
        while (_result.next()){
            array[soItem] = (_result.getString(1));
            soItem++;
        }
        String [] array2 = new String[soItem];
        for (int j = 0; j < soItem; j++) {
                array2[j] = array[j].trim();
        }
        _connect.Close();
        return array2;
    }
    
    public ArrayList TimKiemKhachHangDangthue(KhachHangInfo khachhanginfo, LoaiKhachHangInfo loaikhach) throws Exception{
        ArrayList vt = new ArrayList();
        String condition = "";
        String field = " KHACHHANG.TenKhachHang, LOAIKHACHHANG.TenLoaiKhach, "
                + " PHONG.TenPhong,  convert(varchar, PHIEUTHUEPHONG.NgayThuePhong, 103), "
                + " KHACHHANG.CMND, KHACHHANG.DiaChi";
        String table = " PHONG, LOAIKHACHHANG, KHACHHANG, PHIEUTHUEPHONG, CHITIETPHIEUTHUE, HOADON ";
        
        
        if(!khachhanginfo.getTenKhachHang().equals("")){
            condition += " and KHACHHANG.TenKhachHang like N'%" + khachhanginfo.getTenKhachHang() + "%'";
        }
        
        if(!khachhanginfo.getCMND().equals("")){
            condition += " and KHACHHANG.CMND like N'%" + khachhanginfo.getCMND() + "%'";
        }
        
        if(!loaikhach.getTenLoaiKhach().equals("")){
            condition += " and LOAIKHACHHANG.TenLoaiKhach like N'%" + loaikhach.getTenLoaiKhach() + "%'";
        }
        
        String con = " PHONG.MaPhong = PHIEUTHUEPHONG.MaPhong "
                + " and CHITIETPHIEUTHUE.MaPhieuThue =  PHIEUTHUEPHONG.MaPhieuThue"
                + " and CHITIETPHIEUTHUE.MaKhachHang = KHACHHANG.MaKhachHang "
                + " and HOADON.MaKhachHang != KHACHHANG.MaKhachHang "
                + " and KHACHHANG.MaLoaiKhach = LOAIKHACHHANG.MaLoaiKhach " + condition;
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select(field, table, con, "TenKhachHang ASC");
        int stt = 0;
        String ngaytra = "Đang ở";
        while (_result.next()){
            ArrayList vt_row = new ArrayList();
            stt++;
            vt_row.add(stt);
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(ngaytra);
            vt_row.add(_result.getString(5));
            vt_row.add(_result.getString(6));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public ArrayList TimKiemKhachHangDatra(KhachHangInfo khachhanginfo, LoaiKhachHangInfo loaikhach) throws Exception{
        ArrayList vt = new ArrayList();
        String condition = "";
        String field = " KHACHHANG.TenKhachHang, LOAIKHACHHANG.TenLoaiKhach, "
                + " PHONG.TenPhong,  convert(varchar, PHIEUTHUEPHONG.NgayThuePhong, 103), "
                + "  convert(varchar, HOADON.NgayThanhToan, 103), KHACHHANG.CMND, KHACHHANG.DiaChi";
        String table = " PHONG, LOAIKHACHHANG, KHACHHANG, PHIEUTHUEPHONG, CHITIETPHIEUTHUE, HOADON ";
        
        
        
        if(!khachhanginfo.getTenKhachHang().equals("")){
            condition += " and KHACHHANG.TenKhachHang like N'%" + khachhanginfo.getTenKhachHang() + "%'";
        }
        
        if(!khachhanginfo.getCMND().equals("")){
            condition += " and KHACHHANG.CMND like N'%" + khachhanginfo.getCMND() + "%'";
        }
        
        if(!loaikhach.getTenLoaiKhach().equals("")){
            condition += " and LOAIKHACHHANG.TenLoaiKhach like N'%" + loaikhach.getTenLoaiKhach() + "%'";
        }
        
        String con = " PHONG.MaPhong = PHIEUTHUEPHONG.MaPhong "
                + " and CHITIETPHIEUTHUE.MaPhieuThue =  PHIEUTHUEPHONG.MaPhieuThue"
                + " and CHITIETPHIEUTHUE.MaKhachHang = KHACHHANG.MaKhachHang "
                + " and HOADON.MaKhachHang = KHACHHANG.MaKhachHang "
                + " and KHACHHANG.MaLoaiKhach = LOAIKHACHHANG.MaLoaiKhach " + condition;
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select(field, table, con, "TenKhachHang ASC");
        int stt = 0;
        while (_result.next()){
            ArrayList vt_row = new ArrayList();
            stt++;
            vt_row.add(stt);
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(_result.getString(5));
            vt_row.add(_result.getString(6));
            vt_row.add(_result.getString(7));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
}
